package leave.dataaccess.mysql;

import java.sql.*;

import java.util.logging.Level;
import java.util.logging.Logger;

import leave.dataaccess.LeaveCountDAO;
import leave.exception.DataAccessException;
import leave.model.LeaveCount;
import leave.model.impl.mysql.LeaveCountImpl;


public class LeaveCountDAOImpl implements LeaveCountDAO {

	private Connection FactoryConnection;

	public LeaveCountDAOImpl(Connection factoryConnection) {
		super();
		FactoryConnection = factoryConnection;
	}

	@Override
	public LeaveCount newLeaveCount() {
		return new LeaveCountImpl();
	}
	
	/*
	 * (non-Javadoc)
	 * @see leave.dataaccess.LeaveCountDAO#getLeaveCount(java.lang.String)
	 * Get number of days of leave qualified for a specified rank and leave type.
	 */
	@Override
	public LeaveCount getLeaveCount(String rank, String leaveType) throws DataAccessException{
		// TODO Add code here
		String queryCommand = "select number_of_days from leave_count" +
							  "where rand_id = ?" +
							  "and leave_type_id = ?;";

		LeaveCountImpl oneLeave = new LeaveCountImpl();
		PreparedStatement st = null;
		try{
			st = FactoryConnection.prepareStatement(queryCommand);
			st.setString(1, rank);
			st.setString(2, leaveType);

			ResultSet rs = st.executeQuery();
			
			while (rs.next()) {
				oneLeave.setRank(rank);
				oneLeave.setLeaveType(leaveType);
				oneLeave.setNumberOfDays(rs.getInt("number_of_days"));
			}

		} catch (Exception e) {
			String error = "Error selecting LeaveCount. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DataAccessException(error);
		} finally {
			try {
				st.close();
			} catch (SQLException e) {
				throw new DataAccessException("Unable to carry out transaction.", e);
			}
		}
		return oneLeave;
	} 

	/*
	 * (non-Javadoc)
	 * @see leave.dataaccess.LeaveCountDAO#addLeaveCount(leave.model.LeaveCount)
	 * Add the new qualification of number of days of leave to the database.
	 */
	@Override
	public void addLeaveCount(LeaveCount lc) throws DataAccessException {
		// TODO Add code here
		Statement st = null;
		try{
			String addSql = "INSERT INTO LeaveCount VALUES('" + lc.getRank()
			+ "', '" + lc.getRank()+"', '"+ lc.getNumberOfDays()
			+ "');";

			st = FactoryConnection.createStatement();

			st.executeUpdate(addSql);
		} catch (Exception e) {
			String error = "Error selecting LeaveCount. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DataAccessException(error);
		} finally {
			try {
				st.close();
			} catch (Exception e) {
				throw new DataAccessException("Unable to carry out transaction.", e);
			}
		}
	}
	
	/*
	 * (non-Javadoc)
	 * @see leave.dataaccess.LeaveCountDAO#updateLeaveCount(leave.model.LeaveCount)
	 * Update the LeaveCount object to the database.
	 */
	@Override
	public void updateLeaveCount(LeaveCount lc)throws DataAccessException {
		// TODO Add code here
		Statement st = null;
		try{
			String updateSql = "UPDATE LeaveCount SET number_of_days = '"
				+ lc.getNumberOfDays() + "', leave_type_id = '" + lc.getLeaveType()
				+ "' WHERE rank_id='" + lc.getRank() + "';";

			st = FactoryConnection.createStatement();
			st.executeUpdate(updateSql);

		} catch (Exception e) {
			String error = "Error selecting LeaveCount. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DataAccessException(error);
		} finally {
			try {
				st.close();
			} catch (Exception e) {
				throw new DataAccessException("Unable to carry out transaction.", e);
			}
		}
	}

	/*
	 * (non-Javadoc)
	 * @see leave.dataaccess.LeaveCountDAO#deleteLeaveCount(leave.model.LeaveCount)
	 * Delete the LeaveCount data from the database.
	 */
	@Override
	public void deleteLeaveCount(LeaveCount lc) throws DataAccessException{

		// TODO Add code here

		String deleteCommand = "Delete from leave_count" +
								"where rank_id = ?" +
								"and leave_type_id = ?;";
		PreparedStatement st = null;
		try {
			st = FactoryConnection.prepareStatement(deleteCommand);

			// TODO: set variable values here
			/*
			st.setInt();	// rank_id (1)
			st.setInt();	// leave_type_id (2)
			*/

			st.executeUpdate();

		} 
		catch (Exception e) {
			String error = "Error deleting LeaveCount. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DataAccessException(error);
		} 
		finally {
			try {
				st.close();
			} 
			catch (Exception e) {
				throw new DataAccessException("Unable to carry out transaction.", e);
			}
		}
	}
	
}



